package com.itheima.leyou.dao;

import io.netty.util.ResourceLeakDetector;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementCreator;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Map;

@Repository
public class StorageDao {

    @Resource
    JdbcTemplate jdbcTemplate;

    public ArrayList<Map<String, Object>> getStockStorage(String sku_id){
        //1、创建SQL
        String sql = "select sku_id, quanty " +
                "from tb_stock_storage where sku_id = "+sku_id;

        //2、查询SQL
        return (ArrayList<Map<String, Object>>) jdbcTemplate.queryForList(sql);
    }

    public Map<String, Object> insertStorage(String sku_id, double in_quanty, double out_quanty){
        Map<String, Object> resultMap = new HashMap<String, Object>();
        //1、查询库存主表是否有数据，需要id值
        String sql = "select id from tb_stock_storage where sku_id = "+sku_id;
        ArrayList<Map<String, Object>> list = (ArrayList<Map<String, Object>>) jdbcTemplate.queryForList(sql);

        int new_id = 0;
        //2、如果库存主表有值，那么直接取id
        double quanty = in_quanty - out_quanty;
        boolean result = false;
        boolean needUpdate = false;

        if (list!=null&&!list.isEmpty()){
            new_id = Integer.parseInt(list.get(0).get("id").toString());
            needUpdate = true;
        }else {
            //3、如果库存主表没有值，则写入库存主表，并取得id

            KeyHolder keyHolder = new GeneratedKeyHolder();
            sql = "INSERT INTO tb_stock_storage (warehouse_id, sku_id, quanty) " +
                    "VALUES (1, "+sku_id+", "+quanty+")";
            final String finalSql = sql;
            result = jdbcTemplate.update(new PreparedStatementCreator() {
                public PreparedStatement createPreparedStatement(Connection connection) throws SQLException {
                    PreparedStatement preparedStatement = connection.prepareStatement(finalSql, Statement.RETURN_GENERATED_KEYS);
                    return preparedStatement;
                }
            }, keyHolder)==1;
            new_id = keyHolder.getKey().intValue();

            //3.1、判断如果写入主表失败，则返回false数据
            if (!result){
                resultMap.put("result",false);
                resultMap.put("msg", "写入库存主表失败了！");
                return resultMap;
            }
        }

        //4、通过库存主表id写入历史表
        sql = "INSERT INTO tb_stock_storage_history (stock_storage_id, in_quanty, out_quanty) " +
                "VALUES ("+new_id+", "+in_quanty+", "+out_quanty+")";
        result = jdbcTemplate.update(sql)==1;

        //4.1、判断如果写入历史表失败，则返回false数据
        if (!result){
            resultMap.put("result",false);
            resultMap.put("msg", "写入库存历史表失败了！");
            return resultMap;
        }

        //5、如果库存主表有值，再次更新主表的数量
        if (needUpdate){
            sql = "UPDATE tb_stock_storage SET quanty = quanty + "+quanty+" WHERE id = "+new_id;
            result = jdbcTemplate.update(sql)==1;

            //5.1、判断如果更新失败，则返回false数据
            if (!result){
                resultMap.put("result",false);
                resultMap.put("msg", "更新库存主表失败了！");
                return resultMap;
            }
        }

        //6、返回true数据
        resultMap.put("result", true);
        resultMap.put("msg", "");
        return resultMap;

    }
}
